
create database OADatabase
go

use OADatabase
go


/*==============================================================*/
/* DBMS name:      Sybase SQL Anywhere 12                       */
/* Created on:     2021/10/26 9:39:43                           */
/*==============================================================*/


if exists(select 1 from sys.sysforeignkey where role='FK_APPLICAT_REFERENCE_USERINFO') then
    alter table ApplicationInfo
       delete foreign key FK_APPLICAT_REFERENCE_USERINFO
end if;

if exists(select 1 from sys.sysforeignkey where role='FK_USERINFO_REFERENCE_YUANGONI') then
    alter table UserInfo
       delete foreign key FK_USERINFO_REFERENCE_YUANGONI
end if;

drop table if exists ApplicationInfo;

drop table if exists UserInfo;

drop table if exists YuanGonInfo;

/*==============================================================*/
/* Table: ApplicationInfo                                       */
/*==============================================================*/
create table ApplicationInfo 
(
   AID                  int                            not null,
   UID                  int                            null,
   Adatetime            datetime                       null,
   Day                  varchar(80)                    null,
   APeople              int                            null,
   ASite                nvarchar(80)                   null,
   AMoney               money                          null,
   ALog                 nvarchar(80)                   null default '已申请',
   constraint PK_APPLICATIONINFO primary key clustered (AID),
   constraint CKT_APPLICATIONINFO check ( ALog='已申请' or ALog='已拒绝' or ALog='已受理' or ALog='延期处理' or ALog='已过期')
);

/*==============================================================*/
/* Table: UserInfo                                              */
/*==============================================================*/
create table UserInfo 
(
   UID                  int                            not null,
   YID                  int                            null,
   UName                nvarchar(80)                   null,
   UBank                nvarchar(80)                   null,
   UPhone               int                            null,
   UAddress             nvarchar(80)                   null,
   constraint PK_USERINFO primary key clustered (UID)
);

/*==============================================================*/
/* Table: YuanGonInfo                                           */
/*==============================================================*/
create table YuanGonInfo 
(
   YID                  int                            not null,
   YName                nvarchar(80)                   null,
   YZhiWei              nvarchar(80)                   null,
   YPhone               int                            null,
   YMoney               money                          null,
   constraint PK_YUANGONINFO primary key clustered (YID)
);

alter table ApplicationInfo
   add constraint FK_APPLICAT_REFERENCE_USERINFO foreign key (UID)
      references UserInfo (UID)
      on update restrict
      on delete restrict;

alter table UserInfo
   add constraint FK_USERINFO_REFERENCE_YUANGONI foreign key (YID)
      references YuanGonInfo (YID)
      on update restrict
      on delete restrict;


--题目3（10分）：当业务员受理用户申请的时候，需要将当前处理的记录中受理标志修改为：已受理，同时将其它所有状态为：已申请，且会议日期已超过的记录中的受理标志个性为：已过期，请设计一个存储过程来处理此业务；

go
create proc proc_UserInfo
as 
select * from UserInfo